﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FOModels.Articles;
using System.Data;
namespace DataAccess.Articles
{
    public class ArticlesDAL
    {
        private SQLDataAccess db;
        public ArticlesDAL()
        {
            db = new SQLDataAccess();
        }
        public DataTable getArticlesCate(int langid)
        {
            return db.exeSelect("Select * from ArticlesCate ca join ArticlesCateLang cl on ca.ArticlesCateID = cl.ArticlesCateID where LangID = " + langid + "");
        }
        public DataTable getArticlesByID(int ArticlesID, int langid)
        {
            return db.exeSelect("select * from Articles ca join ArticlesLang cal on ca.ArticlesID = cal.ArticlesID where ca.ArticlesID = " + ArticlesID + " and LangID = " + langid + "");
        }

        public DataTable getArticlesByIDShow(int ArticlesID, int langid)
        {
            return db.exeSelect("select * from Articles ca join ArticlesLang cal on ca.ArticlesID = cal.ArticlesID where ca.ArticlesID = " + ArticlesID + " and LangID = " + langid + " and ca.Display = 1");
        }
        public DataTable getArticlesByCateIDShow(int ArticlesCateID, int langid)
        {
            return db.exeSelect("select * from Articles ca join ArticlesLang cal on ca.ArticlesID = cal.ArticlesID where ca.ArticlesCateID = " + ArticlesCateID + " and LangID = " + langid + " and ca.Display = 1 order by ca.DatePost desc");
        }
        public DataTable getall(int langid)
        {
            return db.exeSelect("select * from Articles ca join ArticlesLang cal on ca.ArticlesID = cal.ArticlesID join ArticlesCate ac on ca.ArticlesCateID = ac.ArticlesCateID join ArticlesCateLang acl on ac.ArticlesCateID = acl.ArticlesCateID join [Language] la on cal.LangID = la.ID   where cal.LangID = " + langid + " and acl.LangID = " + langid + " and la.ID = " + langid + "");
        }
        public DataTable getTOP1()
        {
            return db.exeSelect("Select TOP 1 * from Articles order by ArticlesID desc");
        }
        public int UpdateArticles(ArticlesModels models)
        {
            return db.exeUpdate("update Articles set Images = '" + models.Images + "',UserUpdate='" + models.UserUpdate + "',DateUpdate='" + models.DateUpdate + "',Display='" + models.Display + "',ArticlesCateID=" + models.ArticlesCateID + "where ArticlesID = " + models.ArticlesID + "");
        }
        public int UpdateArticlesLang(ArticlesModels models, int langid)
        {
            return db.exeUpdate("update ArticlesLang set Title = N'" + models.Title + "', Summary = N'" + models.Summary + "',Content=N'" + models.Content + "' where ArticlesID = " + models.ArticlesID + " and LangID=" + langid + "");
        }
        public int InsertArticles(ArticlesModels models)
        {
            return db.exeUpdate("insert into Articles(Images,UserPost,Display,ArticlesCateID) values ('" + models.Images + "','" + models.UserPost + "','" + models.Display + "'," + models.ArticlesCateID + ")");
        }
        public int DeleteArticles(int ArticlesID)
        {
            return db.exeUpdate("Delete from Articles where ArticlesID = " + ArticlesID + "");
        }

        public int InsertArticlesLang(ArticlesModels models, int langid)
        {
            return db.exeUpdate("insert into ArticlesLang values (" + models.ArticlesID + "," + langid + ",N'" + models.Title + "',N'" + models.Summary + "',N'" + models.Content + "')");
        }
        public int DeleteArticlesLang(int ArticlesID)
        {
            return db.exeUpdate("Delete from ArticlesLang where ArticlesID = " + ArticlesID + "");
        }
    }
}
